package DAO;

import DTO.Item;
import Exception.KwickKioskDBException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;

/**
 * This class represents abstraction for data access from item table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class ItemDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(ItemDAO.class.getName());

    /**
     * constructor for this class.
     * @param conn connection object
     */
    public ItemDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method adds a new item to inventory
     * @param i item data transfer object
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addItem(Item i) throws KwickKioskDBException  {
        String sqlText = "INSERT INTO item "
                + "(upc, itemName, costPrice, retailPrice, "
                + "description, make, model, imgURL, ctid) VALUES "
                + "('" + i.getUpc() + "', "
                + "'" + i.getName() + "', "
                + "'" + i.getCostPrice() + "', "
                + "'" + i.getRetailPrice() + "', "
                + "'" + i.getDescription() + "', "
                + "'" + i.getMake() + "', "
                + "'" + i.getModel() + "', "
                + "'" + i.getImgURL() + "', "
                + i.getCategoryID() + ")";
        return updateDB(sqlText);
    }

    /**
     * This method deletes an item from database
     * @param upc a string representing item  code
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean deleteItem(String upc) throws KwickKioskDBException {
        String sqlText = "DELETE FROM item WHERE upc='"
                + upc + "'";
        return updateDB(sqlText);
    }
    /**
     * his methods updates item information into database
     * @param originalUPC  item code in database
     * @param i item data transfer object
     * @return   boolean flag
     * @throws KwickKioskDBException
     */
    public boolean updateItem(String originalUPC, Item i)
            throws KwickKioskDBException {

        String sqlText = "UPDATE item SET "
                + "upc='" + i.getUpc() + "', "
                + "itemName='" + i.getName() + "', "
                + "costPrice='" + i.getCostPrice() + "', "
                + "retailPrice='" + i.getRetailPrice() + "', "
                + "description='" + i.getDescription() + "', "
                + "make='" + i.getMake() + "', "
                + "model='" + i.getModel() + "', "
                + "imgURL='" + i.getImgURL() + "', "
                + "ctid=" + i.getCategoryID() + " "
                + "WHERE upc='" + originalUPC + "'";
        return updateDB(sqlText);
    }

    /**
     * This method adds an item into store
     * @param storeName a string for store name.
     * @param upc a string for item code
     * @param qty an integer for quantity of items
     * @return  a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addItemToStore(String storeName, String upc, int qty)
            throws KwickKioskDBException {

        String sqlText = "INSERT INTO iteminstore "
                + "(storeName, upc, qty) VALUES ("
                + "'" + storeName + "', "
                + "'" + upc + "', "
                + qty + ")";
        return updateDB(sqlText);
    }

    /**
     * This method associates a new item with a promotion
     * @param upc a string for item code
     * @param prid an integer for promotion code
     * @return boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addItemToPromotion(String upc, int prid)
            throws KwickKioskDBException {

        String sqlText = "INSERT INTO hasdiscount "
                + "(upc, prid) VALUES ("
                + "'" + upc + "', "
                + prid + ")";
        return updateDB(sqlText);
    }

    /**
     * This method updates quantity of items in store
     * @param storeName a string for store name
     * @param upc a string for item code
     * @param qty an integer for quantity of items
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean updateItemQTYInStore(String storeName, String upc, int qty)
            throws KwickKioskDBException {

        String sqlText = "UPDATE iteminstore SET qty=" + qty
                + " WHERE storeName='" + storeName + "' and upc='" + upc
                + "'";
        return updateDB(sqlText);
    }

    /**
     * This method searches an item in database by item name as criteria
     * @param keywords string representing search criteria
     * @param storeName string for store name
     * @return array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByName(String keywords, String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist "
                    + "where "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc";
            StringTokenizer st = new StringTokenizer(keywords.trim());

            if (st.hasMoreTokens()) {
                sqlText += " and ";
            }

            while (st.hasMoreTokens()) {
                sqlText += "it.itemName like '%" + st.nextToken() + "%'";
                if (st.hasMoreTokens()) {
                    sqlText += " and ";
                }
            }

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : searchByName" + ex);
            throw new KwickKioskDBException("ItemDAO : searchByName",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database by item code as search criteria.
     * @param upc a string for item code
     * @param storeName a string for store name
     * @return
     */
    public Item searchByUPC(String upc, String storeName) {
        Item result = null;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist "
                    + "where it.upc='" + upc + "' and "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Item newItem = new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9));
                sqlStatement.close();
                return newItem;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : searchWithKeyWord" + ex);
        }
        return null;
    }

    /**
     * This method retrieves item information from database using item manufacturer as criteria.
     * @param make  a string representing item manufacturer
     * @param storeName  a string representing store name
     * @return an array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByMake(String make, String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist "
                    + "where it.make='" + make + "' and "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            System.out.println("result set is"+rs);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
           // ex.printStackTrace();
           // logger.log(Level.SEVERE,  "ItemDAO : searchByMake" + ex.printStackTrace());
            throw new KwickKioskDBException("ItemDAO : searchByMake",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database using item model as criteria.
     * @param model a string
     * @param storeName a string
     * @return an array list object of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByModel(String model, String storeName)
            throws KwickKioskDBException {

        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist "
                    + "where it.model='" + model + "' and "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : searchWithKeyWord" + ex);
            throw new KwickKioskDBException("ItemDAO : searchWithKeyWord",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database using a keyword search.
     * @param keywords a string
     * @param storeName a string
     * @return an array list object of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByDescription(String keywords,
            String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist ";
            StringTokenizer st = new StringTokenizer(keywords.trim());

            if (st.hasMoreTokens()) {
                sqlText += "where "
                        + "ist.storeName='" + storeName + "' and "
                        + "ist.upc=it.upc and ";
            }

            while (st.hasMoreTokens()) {
                sqlText += "it.itemName like '%" + st.nextToken() + "%'";
                if (st.hasMoreTokens()) {
                    sqlText += " and ";
                }
            }

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : searchByName" + ex);
            throw new KwickKioskDBException("ItemDAO : searchByName",
                        ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item based on a category id.
     * @param ctid an integer
     * @param storeName a string
     * @return array list of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByCategory(int ctid, String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist "
                    + "where "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc and "
                    + "it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : getItemByCategory" + ex);
            throw new KwickKioskDBException("ItemDAO : getItemByCategory",
                        ex.getMessage());
        }

        return list;
    }

    /**
     * This method searches items in database by a category id and brand.
     * @param ctid an integer category id
     * @param brand a string
     * @param storeName a string
     * @return an array list of objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByCategoryAndBrand(int ctid,
            String brand,
            String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore is "
                    + "where "
                    + "is.storeName='" + storeName + "' and "
                    + "is.upc=it.upc and "
                    + "it.make='" + brand + "' and " + "it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : getItemByCategory" + ex);
            throw new KwickKioskDBException("ItemDAO : getItemByCategory",
                        ex.getMessage());
        }

        return list;
    }

    /**
     * This method searches items based on a price range criteria.
    * @param ctid an integer
     * @param lower a double value for lower limit
     * @param upper a double value for upper limit
     * @param storeName a string
     * @return a list of item objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByPriceRange(int ctid,
            double lower, double upper,
            String storeName) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it, iteminstore ist where "
                    + "ist.storeName='" + storeName + "' and "
                    + "ist.upc=it.upc and ";


            if (upper != -1.0) {
                sqlText += "it.retailPrice >= " + lower
                        + " and " + "it.retailPrice <= " + upper
                        + " and ";
            } else {
                sqlText += "it.retailPrice >= " + lower + " and ";
            }

            sqlText += "it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : getItemByCategory" + ex);
            throw new KwickKioskDBException("ItemDAO : getItemByCategory",
                        ex.getMessage());
        }

        return list;
    }

    /**
     * This method retrieves quantity of items in a store
     * @param storeName a string for store name
     * @param upc a string for item code
     * @return an integer indicating quantity of an item
     * @throws {@link KwickKioskDBException}
     */
    public int getItemQtyInStore(String storeName, String upc)
            throws KwickKioskDBException {
        int qty = -1;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.qty "
                    + "from iteminstore a "
                    + "where a.storeName = '" + storeName
                    + "' and a.upc='" + upc + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                qty = rs.getInt(1);
                sqlStatement.close();
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : getItemQtyInStore" + ex);
            throw new KwickKioskDBException("ItemDAO : getItemQtyInStore",
                        ex.getMessage());
        }
        return qty;
    }

    /**
     * this methods updates the database
     * @param sqlText a string for data manipulation  query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            sqlStatement.close();
            if (value == 1) {
                conn.commit();
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "ItemDAO : updateDB" + ex);
                throw new KwickKioskDBException("ItemDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "ItemDAO : updateDB" + ex2);
                throw new KwickKioskDBException("ItemDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }

    /**
     * this method retrieves items associated with a store
     * @param storeName string for store name
     * @return
     */
    public ArrayList<Item> getAllItems(String storeName) {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item, iteminstore ist "
                    + "where it.upc=ist.upc and ist.storeName='" + storeName + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
            return list;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ItemDAO : getAllItems" + ex);
        }
        return list;
    }
}
